Start of change

REMOVE_USER_INDEX_ENTRY and REMOVE_USER_INDEX_ENTRY_BINARY table functions

Edit online

The REMOVE_USER_INDEX_ENTRY and REMOVE_USER_INDEX_ENTRY_BINARY table functions remove one or more entries from a user index (*USRIDX).

The values used by the table functions are closely related to the values handled by the Remove User Index Entries (QUSRMVUI) API.

Authorization: The caller must have:
  • *EXECUTE authority to the library containing the user index, and
  • *CHANGE authority to the user index.
Read syntax diagramSkip visual syntax diagramREMOVE_USER_INDEX_ENTRYREMOVE_USER_INDEX_ENTRY_BINARY( USER_INDEX => user-index,USER_INDEX_LIBRARY => user-index-library,OPERATION => operation,REMOVE_VALUE => remove-value,REMOVE_VALUE_END => remove-value-end,MAX_REMOVE => max-remove)

The schema is QSYS2.

user-index
A character string containing the name of the user index for the removal of entries.
user-index-library
A character string containing the name of the library where the user index is located. Can be one of the following special values:
*CURLIB
The current library is used.
*LIBL
The library list is used.
operation
The operation to be used for comparing remove-value and remove-value-end and the index value.
If the index is keyed, the complete entry consists of the key followed by the entry data. If the length of remove-value is less than or equal to the length of the key, the comparison only applies to the key. If the length of remove-value is greater than the key, the comparison will include some or all of the entry data as well.
If the index is not keyed, the comparison applies to the value of the entry data.
EQ
Remove entries that match remove-value or start with remove-value.
GE
Remove entries that are greater than or equal to remove-value.
GT
Remove entries that are greater than remove-value.
LE
Remove entries that are less than or equal to remove-value.
LT
Remove entries that are less than remove-value.
BETWEEN
Remove entries that are greater than or equal to remove-value and less than or equal to remove-value-end.
FIRST
Remove the first n entries from the user index, where n is the max-remove value. If max-remove is not specified, all index entries are removed.
remove-value and remove-value-end are ignored.
LAST
Remove the last n entries from the user index, where n is the max-remove value. If max-remove is not specified, all index entries are removed.
remove-value and remove-value-end are ignored.
remove-value
A string that specifies the value to compare with the index value to determine whether an index entry is to be removed.
  • For REMOVE_USER_INDEX_ENTRY, the input value will be converted to a character string using the job CCSID.
  • For REMOVE_USER_INDEX_ENTRY_BINARY, the input value will be considered a binary string.
This parameter is ignored when operation is FIRST or LAST. It is required for all other operation values.
remove-value-end
A string that specifies the value to compare with the index value as the upper bound for BETWEEN. The lengths of remove-value and remove-value-end must be the same.
  • For REMOVE_USER_INDEX_ENTRY, the input value will be converted to a character string using the job CCSID.
  • For REMOVE_USER_INDEX_ENTRY_BINARY, the input value will be considered a binary string.
This parameter is ignored when operation is not BETWEEN. It is required when operation is BETWEEN.
max-remove
An integer value that specifies the maximum number of user index entries satisfying the remove comparison that should be removed. A value of -1 can be used to remove all index entries that satisfy the remove comparison. The default is -1.

The result of the function is a table containing one row for each index entry that was removed with the format shown in the following table. All columns are nullable.

Table 1. REMOVE_USER_INDEX_ENTRY table function
Column Name Data Type Description
ORDINAL_POSITION INTEGER The relative position of this row in the set of removed index entries.
USER_INDEX_LIBRARY VARCHAR(10) The name of the library where the user index was found.
USER_INDEX VARCHAR(10) The name of the user index.
REMOVED_ENTRY VARCHAR(2000) The data for the index entry that was removed, in character form. The value includes both the key and the entry data.
REMOVED_ENTRY_BINARY VARBINARY(2000) The data for the index entry that was removed, in binary form. This is the raw bytes of data. The value includes both the key and the entry data.

Example

  • Remove all index entries with a key value less than '00173'.
    SELECT * FROM TABLE(QSYS2.REMOVE_USER_INDEX_ENTRY(USER_INDEX => 'USRIX1', 
                                                  USER_INDEX_LIBRARY => 'APPLIB', 
                                                  OPERATION => 'LT', 
                                                  REMOVE_VALUE => '00173'));
End of change